package com.senthink.gl.business.domain.repository;

import com.senthink.gl.business.auth.AreaAuthority;
import com.senthink.gl.business.auth.DeviceAuthority;
import com.senthink.gl.business.domain.entity.*;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

/**
 * 用户区域权限Repository
 *
 * @author zooqi@senthink.com
 * @date 2018/03/05
 */
public interface UserAreaAuthorityRepository extends JpaRepository<UserAreaAuthority, Long> {

    /**
     * 通过用户名查找所有区域权限
     *
     * @param username 用户名
     * @return
     */
    List<UserAreaAuthority> findAllByUserAccountOrderByAreaName(String username);

    /**
     * 通过uid删除
     *
     * @param uid
     * @return
     */
    @Transactional
    long deleteAllByUserUid(String uid);

    /**
     * 通过用户名查询用户具有权限的区域列表
     *
     * @param username 用户名
     * @return
     */
    @Query("SELECT authority.area FROM UserAreaAuthority authority WHERE authority.user.name = ?1")
    List<Area> findAreasByUsername(String username);

    /**
     * 通过账号查询用户具有权限的区域列表
     *
     * @param account 用户名
     * @return
     */
    @Query("SELECT authority.area FROM UserAreaAuthority authority WHERE authority.user.account = ?1")
    List<Area> findAreasByAccount(String account);

    /**
     * 查询区域下的用户(姓名)列表
     *
     * @param area
     * @return
     */
    @Query("SELECT authority.user.name FROM UserAreaAuthority authority WHERE authority.area = ?1")
    List<String> findUserNamesByArea(Area area);

    /**
     * 查询用户具有除霾器权限的区域数据库ID列表
     *
     * @param uid                  UID
     * @param hazeCleanerAuthority 除霾器权限字符串
     * @return
     */
    @Query("SELECT a.area.id FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2%")
    List<Long> findAreaIdsWithHazeCleanerAuthorityByUid(String uid, String hazeCleanerAuthority);

    /**
     * 查询用户具有除霾器权限的区域列表(按区域名称排序)
     *
     * @param uid                  UID
     * @param hazeCleanerAuthority 除霾器权限字符串
     * @return
     */
    @Query("SELECT a.area FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2% ORDER BY a.area.name")
    List<Area> findAreasWithHazeCleanerAuthorityByUid(String uid, String hazeCleanerAuthority);

    /**
     * 查询用户具有除霾器权限的区域列表
     *
     * @param uid                  UID
     * @param hazeCleanerAuthority 除霾器权限字符串
     * @param aids                 区域业务ID列表
     * @return
     */
    @Query("SELECT a.area FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.area.aid IN ?3 AND a.deviceAuthority LIKE %?2%")
    List<Area> findAreasWithHazeCleanerAuthorityByUid(String uid, String hazeCleanerAuthority, Iterable<String> aids);



    /**
     * 查询用户具有权限的网关列表
     *
     * @param account            账户
     * @return
     */
    @Query("SELECT DISTINCT a.area.id FROM UserAreaAuthority a WHERE a.user.account = ?1")
    List<Long> findAreaIdsByAccount(String account);
   /* @Query("SELECT DISTINCT alp.area.id FROM UserAreaAuthority a, AreaLightPole alp WHERE a.user.account = ?1 AND a.area = alp.area")
    List<Long> findAreaIdsByAccount(String account);*/

    /**
     * 查询用户具有权限的网关列表
     *
     * @param account            账户
     * @return
     */
    @Query("SELECT DISTINCT a.area.aid FROM UserAreaAuthority a WHERE a.user.account = ?1")
    List<String> findAreaAidsByAccount(String account);

    /**
     * 查询用户具有权限的网关列表
     *
     * @param account            账户
     * @return
     */
    /*@Query("SELECT DISTINCT alp.area.aid FROM UserAreaAuthority a, AreaLightPole alp WHERE a.user.account = ?1 AND a.area = alp.area")
    List<String> findAreaAidsByAccount(String account);*/





    /**
     * 根据用户账户以及区域aid获取用户的设备列表
     *
     * @param aid
     * @param account
     * @return
     */
    Optional<UserAreaAuthority> findTopByAreaAidAndUserAccount(String aid, String account);

    /**
     * 查询用户具有某设备权限的区域数据库ID列表
     *
     * @param uid             用户UID
     * @param deviceAuthority 设备权限字符串, 参见{@link DeviceAuthority}
     * @return
     */
    @Query("SELECT a.area.id FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2%")
    List<Long> findAreaIdsWithDeviceAuthorityByUid(String uid, String deviceAuthority);

    @Query("SELECT a.area.aid FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2%")
    List<String> findAreaAidsWithDeviceAuthorityByUid(String uid, String deviceAuthority);

    /**
     * 查询用户具有某设备权限的区域数据库ID列表
     *
     * @param uid             用户UID
     * @param deviceAuthority 设备权限字符串, 参见{@link DeviceAuthority}
     * @param areaId          区域数据库ID(筛选条件)
     * @return
     */
    @Query("SELECT a.area.id FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2% AND a.area.id = ?3")
    List<Long> findAreaIdsWithDeviceAuthorityByUid(String uid, String deviceAuthority, Long areaId);

    @Query("SELECT a.area.aid FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2% AND a.area.id = ?3")
    List<String> findAreaAidsWithDeviceAuthorityByUid(String uid, String deviceAuthority, Long areaId);

    /**
     * 查询用户具有某设备权限的区域列表(按区域名称排序)
     *
     * @param uid             用户UID
     * @param deviceAuthority 设备权限字符串, 参见{@link DeviceAuthority}
     * @return
     */
    @Query("SELECT a.area FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2% ORDER BY a.area.name")
    List<Area> findAreasWithDeviceAuthorityByUid(String uid, String deviceAuthority);

    /**
     * 查询用户具有某设备权限的区域的数量
     *
     * @param uid
     * @param deviceAuthority
     * @return
     */
    @Query("SELECT COUNT(a) FROM UserAreaAuthority a WHERE a.user.uid = ?1 AND a.deviceAuthority LIKE %?2%")
    long countAreasWithDeviceAuthorityByUid(String uid, String deviceAuthority);

    /**
     * 查询用户所有区域设备权限(按区域名称排序)
     *
     * @param uid 用户UID
     * @return
     */
    @Query("SELECT new com.senthink.gl.business.auth.AreaAuthority(a.area, a.deviceAuthority) FROM UserAreaAuthority a WHERE a.user.uid = ?1 ORDER BY a.area.name")
    List<AreaAuthority> findUserAreaAuthorities(String uid);

    /**
     * 查询用户区域数据库ID与设备权限的对应关系
     *
     * @param uid 用户UID
     * @return
     */
    @Query("SELECT a.area.id, a.deviceAuthority FROM UserAreaAuthority a WHERE a.user.uid = ?1")
    List<Object[]> findUserAreaIdAuthorityMap(String uid);

    /**
     * 查询具有区域权限的所有用户的手机号码
     *
     * @param areas    区域列表
     * @param roleUser 恒为ROLE_USER
     * @param enable   恒为true
     * @return
     */
    @Query("SELECT DISTINCT a.user.mobile FROM UserAreaAuthority a WHERE a.area IN ?1 AND a.user.role = ?2 AND a.user.enable = ?3")
    List<String> findUserPhonesByAreas(Iterable<Area> areas, String roleUser, boolean enable);
}
